clear all

********************************************************************************



//Load data
////////////////////////////////////////////////////////////////////////////////

local k = 0
global series "114A 114B"

foreach s in "118_Year 1901" "119_Year 1902" "119_Year 1903" "120_Year 1904" "120_Year 1905" "121_Year 1906" "121_Year 1907" "122_Year 1908" "122_Year 1909" "123_Year 1910" "123_Year 1911" "124_Year 1912" "124_Year 1913"   {
local k = `k'+1
display in red "************************ Series `s' (`k')************************ "


//Industries
import excel using "./data sheets/DATA_Series `s'.xlsx", clear sheet("Industries") cellrange(a3) 

***Mine info
{
rename A series
rename B year
rename C arrondissement
rename D ordernr
rename E mine_name
rename F mine_community
rename G operations_active
rename H operations_reserve
rename I operations_construction

rename J l_under_total_n
rename K l_surface_total_n
rename L l_total_n
rename M l_miner_total_n

drop if missing(series)
}

***Production
{
rename N q_fln_t
rename O q_fln_pq
rename P q_fln_p
rename Q q_fat_t
rename R q_fat_pq
rename S q_fat_p
rename T q_halfl_t
rename U q_halfl_pq
rename V q_halfl_p
rename W q_lean_t
rename X q_lean_pq
rename Y q_lean_p
rename Z  q_total_t
rename AA q_total_pq
rename AB q_total_p

rename AC consumption_stock
drop AD

rename AE sales_t
rename AF sales_pq
rename AG sales_p
rename AH consumption_t
rename AI consumption_pq
rename AJ consumption_p
}

***Finances
{
rename AK expenses_l
rename AL expenses_other
rename AM expenses_total
rename AN prixderevient_std_t 
rename AO expenses_spc_prep
rename AP expenses_spc_prem
rename AQ balance_gain
rename AR balance_total
}
rename AS observations

***Extra
{
capture rename AT q_gross_total_t
capture rename AU dummy_gross_q
}


//String variables
////////////////////////////////////////////////////////////////////////////////
capture tostring dummy_gross_q, replace


tempfile industries`k'
save `industries`k'', replace


//Production
import excel using "./data sheets/DATA_Series `s'.xlsx", clear sheet("Production") cellrange(a3) 

***Mine info
{
rename A series
rename B year
rename C arrondissement
rename D ordernr
rename E mine_name
rename F mine_community

drop if missing(series)
}

***Production (2)
rename G avgdays_pit
rename H avgdays_mine
rename I q_total_t_2
rename J exploitation_m
rename K q_total_qm
rename L avgvein_pwr

rename M q_total_qminer
rename N q_total_qinterior
rename O q_total_qall

***Labor
rename P totaldays

rename Q expenses_l_gross
rename R expenses_l_d_insur
rename S expenses_l_d_fine
rename T expenses_l_d_other
rename U expenses_l_d_total
rename V expenses_l_net

rename W l_under_total_w_gross
rename X l_under_total_w_net
rename Y l_surface_total_w_gross
rename Z l_surface_total_w_net
rename AA l_total_w_gross
rename AB l_total_w_net
rename AC l_miner_total_w_gross
rename AD l_miner_total_w_net
drop AE
rename AF l_under_boys12_n
rename AG l_under_boys14_n
rename AH l_under_men_n
rename AI l_under_women21_n
rename AJ l_surface_boys12_n
rename AK l_surface_boys14_n
rename AL l_surface_men_n
rename AM l_surface_girls12_n
rename AN l_surface_girls21_n
rename AO l_surface_women21_n

rename AP observations

/*
//String variables
////////////////////////////////////////////////////////////////////////////////
tostring series, replace
tostring concession_date, replace
tostring avgvein_thickness, replace
tostring l_*_w, replace
tostring avgprod_pit, replace
*/

tempfile production`k'
save `production`k'', replace

/*
//String variables
////////////////////////////////////////////////////////////////////////////////
tostring series, replace
tostring concession_date, replace
tostring avgvein_thickness, replace
tostring l_*_w, replace
tostring avgprod_pit, replace

tempfile data`k'
save `data`k'', replace
*/
}



//Merge series (per sheet)
////////////////////////////////////////////////////////////////////////////////


***Append industries sheets
use `industries2', clear
foreach t of numlist 3/`k' {
display in red "** `t' **"
append using `industries`t''
}
save industries.dta, replace

***Append production sheets
use `production1', clear
foreach t of numlist 2/`k' {
display in red "** `t' **"
append using `production`t''
}
save production.dta, replace


***Organize mines: industries
use industries.dta, clear
//Identify mines
run DO_Mine_id.do
preserve
order mine_id mine_subid_1 mine_subid_2 mine_subid_3 mine_subid_4
*keep if missing(mine_id)
mdesc mine_id //OK
restore
save industries.dta, replace

***Organize mines: industries
use production.dta, clear
//Minor data edits
drop if mine_name == "Mine de Longvilly"
replace mine_name = subinstr(mine_name,"Charbonnage de ","", .) if strpos(mine_name,"Charbonnage de")>0
replace mine_name = subinstr(mine_name,"Charbonnage d'","", .) if strpos(mine_name,"Charbonnage d'")>0
replace mine_name = subinstr(mine_name,"Charbonnage du","Le", .) if strpos(mine_name,"Charbonnage du")>0
//Identify mines
run DO_Mine_id.do
preserve
order mine_id mine_subid_1 mine_subid_2 mine_subid_3 mine_subid_4
*keep if missing(mine_id)
mdesc mine_id //OK
restore
save production.dta, replace


***Merge!
use industries, clear
mdesc q_total_t //All mines with production appear to be covered in production!

merge 1:1 mine_id year using production.dta, gen(merge_indprod)
drop if year == 1901 //Missing year (industries sheets)

gen noprod = (missing(q_total_t))
tab noprod merge_indprod

erase industries.dta
erase production.dta

***Checks:

*Check 1:
preserve
keep if !missing(q_total_t) & merge_indprod!=3 | !missing(q_total_t_2) & merge_indprod!=3
order mine_id merge_indprod q_total_t*
sort year mine_id 
list mine_name mine_id merge_indprod year //OK
restore

*Check 2
preserve
gen testsum = q_total_t - q_total_t_2
tab testsum //OK
restore


////////////////////////////////////////////////////////////////////////////////
save data_118-124.dta, replace
////////////////////////////////////////////////////////////////////////////////
exit





